﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using emp.Model.SysManage;
using Net.SourceForge.Koogra.Excel;
using org.in2bits.MyXls;
using System.Collections;

public partial class crm_salienceMember_EducePage : System.Web.UI.Page
{
    private DataTable getTableInfo(string sql)
    {
        DataSet data = new DataSet();
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            try
            {
                sda.SelectCommand = new SqlCommand();
                sda.SelectCommand.CommandText = sql;
                sda.SelectCommand.Connection = new SqlConnection(emp.SqlServerDAL.SQLHelper.CONN_STRING_NON_DTC);
                sda.Fill(data);
            }
            catch { }
        }
        return data.Tables[0];
    }


    private bool ExecSql(string sql)
    {
        bool ret = false;
        using (SqlConnection conn = new SqlConnection(emp.SqlServerDAL.SQLHelper.CONN_STRING_NON_DTC))
        {
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    emp.SqlServerDAL.SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, null);
                    trans.Commit();
                    ret = true;
                }
                catch (SqlException err)
                {
                    trans.Rollback();
                }
            }
        }
        return ret;
    }


    public static DataTable ExcelToDS(string path)
    {
        Net.SourceForge.Koogra.Excel.Workbook wb = new Net.SourceForge.Koogra.Excel.Workbook(path);
        DataTable dt = new DataTable();
        foreach (Net.SourceForge.Koogra.Excel.Worksheet ws1 in wb.Sheets)
        {
            Net.SourceForge.Koogra.Excel.Worksheet ws = wb.Sheets[0];
            string[] str = new string[ws.Rows[0].Cells.LastCol + 1];
            for (int i = 0; i < ws.Rows[0].Cells.LastCol + 1; i++)
            {
                str[i] = ws.Rows[0].Cells[(byte)i].Value.ToString();
                dt.Columns.Add(ws.Rows[0].Cells[(byte)i].Value.ToString(), typeof(string));
            }
            for (int r = ws.Rows.FirstRow; r <= ws.Rows.LastRow; ++r)
            {
                Net.SourceForge.Koogra.Excel.Row row = ws.Rows[(ushort)r];
                if (row != null)
                {

                    dt.Rows.Add(r);
                    for (int c = row.Cells.FirstCol; c <= row.Cells.LastCol; ++c)
                    {
                        Net.SourceForge.Koogra.Excel.Cell cell = row.Cells[(byte)c];
                        if (cell != null)
                        {
                            dt.Rows[r][str[c]] = cell.Value;
                        }
                    }
                }
            }


        }
        dt.Rows[0].Delete();
        return dt;
    }

    protected void Page_Load(object sender, EventArgs e)
    {

    }


    private void inset(string IRB_XLS_FILE, string IRB_STATUS, int IRB_UPLOAD_NUM, string IRB_UPLOAD_USER, string IRB_REMARK) 
    {
        string sql = "insert into INDIVIDUAL_RESPONSE_BATCH(IRB_XLS_FILE      ,IRB_STATUS      ,IRB_UPLOAD_NUM      ,IRB_UPLOAD_USER      ,IRB_UOLOAD_DATE      ,IRB_REMARK)values('" + IRB_XLS_FILE + "','" + IRB_STATUS + "','" + IRB_UPLOAD_NUM + "','" + IRB_UPLOAD_USER + "',getdate(),'" + IRB_REMARK + "') ";

        bool bo= ExecSql(sql);

    }



    protected void upLaodOK_Click(object sender, EventArgs e)
    {
        string fileExt = "";
        Label1.Text = "";
        string uploadTime = DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss");//获得上传时间
        if (FileUpload1.HasFile)
        {

            fileExt = System.IO.Path.GetExtension(FileUpload1.FileName);
            IList list = new ArrayList();

            if (fileExt == ".xls" )
            {
                int fu1 = FileUpload1.PostedFile.ContentLength / 1024;

                if (fu1 > 2048)
                {
                    Label1.Text = "Excel太大！";
                    return;
                }
               
                try
                {
                    string patex = HttpContext.Current.Server.MapPath("excel/");

                    FileUpload1.SaveAs(patex + uploadTime + fileExt);

                    UserInfo myUserInfo = new UserInfo();
                    if ((UserInfo)Session["USER_KEY"] != null)
                    {
                        myUserInfo = (UserInfo)Session["USER_KEY"];

                    }

                    string IRB_XLS_FILE =uploadTime + fileExt;
                    string IRB_UPLOAD_USER = myUserInfo.UserID.ToString();

                    string IRB_STATUS = "";
                    int IRB_UPLOAD_NUM = 0;

                    string IRB_REMARK = "";


                    try
                    {
                        string excalPath = patex + uploadTime + fileExt;
                        int ExcelRows =100000;
                        System.Data.DataTable dt = ExcelToDS(excalPath);//根据保存的文件名读取Excel文件将数据放入DataSet中
                        if (dt.Columns.Count != 10)
                        {
                            IRB_REMARK = "表格列数有误";
                            IRB_STATUS = "处理失败";

                            inset(IRB_XLS_FILE, IRB_STATUS, IRB_UPLOAD_NUM, IRB_UPLOAD_USER, IRB_REMARK);


                            return;
                        }
                        if (dt.Rows.Count > ExcelRows || dt.Rows.Count == 0)
                        {
                            IRB_REMARK = "导入行数有误";
                            IRB_STATUS = "处理失败";
                            inset(IRB_XLS_FILE, IRB_STATUS, IRB_UPLOAD_NUM, IRB_UPLOAD_USER, IRB_REMARK);
                            return ;
                        }
                        if (dt == null)
                        {
                            IRB_REMARK = "导入数据无法读取";
                            IRB_STATUS = "处理失败";
                            inset(IRB_XLS_FILE, IRB_STATUS, IRB_UPLOAD_NUM, IRB_UPLOAD_USER, IRB_REMARK);
                            return ;
                        }

                        // int asd = 0;
                        int num = 0;

                        string[] strsz = { "活动代号", "短信唯一码", "经销商代号", "客户编码", "客户姓名", "客户手机号", "是否参加活动", "是否有购买行为", "购买的产品", "是否对活动满意", "错误提示" };
                        string subStr = "cw" + uploadTime;

                        //创建table的第一列
                        DataColumn dc = new DataColumn();
                        //该列的数据类型
                        dc.DataType = System.Type.GetType("System.String");
                        //该列得名称
                        dc.ColumnName = "remark";
                        dt.Columns.Add(dc);
                        bool bo = true;
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            num = i + 1;
                            if (dt.Rows[i][0].ToString() == "")
                            {
                                num = i;
                                break;
                            }

                            if (dt.Rows[i][0].ToString().Length>20)
                            {
                                dt = UpRxcel(dt, "活动代号太长", i);
                                IRB_REMARK = "导入数据有误";
                                bo = false;
                            }
                            if (dt.Rows[i][1].ToString().Length > 20)
                            {
                                dt = UpRxcel(dt, "短信唯一码太长", i);
                                IRB_REMARK = "导入数据有误";
                                bo = false;
                            }
                            if (dt.Rows[i][2].ToString().Length > 20)
                            {
                                dt = UpRxcel(dt, "经销商代号太长", i);
                                IRB_REMARK = "导入数据有误";
                                bo = false;
                            }
                            if (dt.Rows[i][3].ToString().Length > 50)
                            {
                                dt = UpRxcel(dt, "客户编码太长", i);
                                IRB_REMARK = "导入数据有误";
                                bo = false;
                            }
                            if (dt.Rows[i][4].ToString().Length > 50)
                            {
                                dt = UpRxcel(dt, "客户姓名太长", i);
                                IRB_REMARK = "导入数据有误";
                                bo = false;
                            }
                            if (dt.Rows[i][6].ToString().Length > 10)
                            {
                                dt = UpRxcel(dt, "是否参加活动太长", i);
                                IRB_REMARK = "导入数据有误";
                                bo = false;
                            }
                            if (dt.Rows[i][7].ToString().Length > 10)
                            {
                                dt = UpRxcel(dt, "是否有购买行为太长", i);
                                IRB_REMARK = "导入数据有误";
                                bo = false;
                            }
                            if (dt.Rows[i][8].ToString().Length > 50)
                            {
                                dt = UpRxcel(dt, "购买的产品太长", i);
                                IRB_REMARK = "导入数据有误";
                                bo = false;
                            }
                            if (dt.Rows[i][9].ToString().Length > 50)
                            {
                                dt = UpRxcel(dt, "是否对活动满意太长", i);
                                IRB_REMARK = "导入数据有误";
                                bo = false;
                            }




                            if (dt.Rows[i][5].ToString() != "" && dt.Rows[i][5].ToString().Length!=11)
                            {
                                dt = UpRxcel(dt, "手机格式有误", i);
                                IRB_REMARK = "导入数据有误";
                                bo = false;
                            }






                        }
                        
                        if (bo)
                        {
                            IRB_STATUS = "处理完成";
                            IRB_UPLOAD_NUM = num;
                            inset(IRB_XLS_FILE, IRB_STATUS, IRB_UPLOAD_NUM, IRB_UPLOAD_USER, IRB_REMARK);

                            string sql = "select IRB_UID from INDIVIDUAL_RESPONSE_BATCH where IRB_XLS_FILE='" + IRB_XLS_FILE + "'";
                            System.Data.DataTable dt1 = getTableInfo(sql);




                            //验证数据全部通过
                            for (int j = 0; j < dt.Rows.Count; j++)
                            {
                                string sql11 = "  insert into INDIVIDUAL_RESPONSE_DETAIL(IRD_IRB_UID,IRD_CAM_CODE,IRD_UNIQUE_CODE,IRD_DEALER_CODE,IRD_CUSTOMER_CODE,IRD_CUSTOMER_NAME,IRD_CELLPHONE,IRD_ATTEND_CAMPAIGN,IRD_IS_PURCHASE,IRD_PURCHASE_PRODUCT,IRD_SATISFIED)values('" + dt1.Rows[0][0].ToString() + "','" + dt.Rows[j][0].ToString() + "','" + dt.Rows[j][1].ToString() + "','" + dt.Rows[j][2].ToString() + "','" + dt.Rows[j][3].ToString() + "','" + dt.Rows[j][4].ToString() + "','" + dt.Rows[j][5].ToString() + "','" + dt.Rows[j][6].ToString() + "','" + dt.Rows[j][7].ToString() + "','" + dt.Rows[j][8].ToString() + "','" + dt.Rows[j][9].ToString() + "')  ";
                                bool bo111 = ExecSql(sql11);

                                sql = "select * from [INDIVIDUALS] where [IND_CUSTOMER_CODE]='" + dt.Rows[j][3].ToString() + "'";
                                System.Data.DataTable dt111 = getTableInfo(sql);
                                if (dt111.Rows.Count > 0)
                                {
                                    sql11 = "  update INDIVIDUALS  set IND_CELLPHONE='" + dt.Rows[j][5].ToString() + "',IND_NAME='" + dt.Rows[j][4].ToString() + "'   where  [IND_CUSTOMER_CODE]='" + dt.Rows[j][3].ToString() + "'";
                                    bo111 = ExecSql(sql11);

                                    sql = "select [CAM_UID] from [CAMPAIGN] where [CAM_CODE]='" + dt.Rows[j][0].ToString() + "'";
                                    System.Data.DataTable dt1111 = getTableInfo(sql);
                                    sql = "select [IRD_UID] from [INDIVIDUAL_RESPONSE_DETAIL] where [IRD_CELLPHONE]='" + dt.Rows[j][5].ToString() + "' and [IRD_IRB_UID]='" + dt1.Rows[0][0].ToString() + "'";
                                    System.Data.DataTable dt11111 = getTableInfo(sql);

                                    if (dt1111.Rows.Count > 0 && dt11111.Rows.Count > 0)
                                    {
                                        sql11 = " insert into [INDIVIDUAL_RESPONSE_FIELDS]([IRF_IND_URN],[IRF_CAM_UID],[IRF_IRD_UID],[IRF_CREATE_DATE])values(" + dt111.Rows[0]["IND_URN"].ToString() + "," + dt1111.Rows[0]["CAM_UID"].ToString() + "," + dt11111.Rows[0]["IRD_UID"].ToString() + ",getdate())";
                                        bo111 = ExecSql(sql11);
                                    }

                                    

                                }
                                //else 
                                //{
                                //    sql11 = "  insert into INDIVIDUALS(IND_CELLPHONE,IND_CUSTOMER_CODE,IND_NAME)values('" + dt.Rows[j][5].ToString() + "','" + dt.Rows[j][3].ToString() + "','" + dt.Rows[j][4].ToString() + "'')  ";
                                //     bo111 = ExecSql(sql11);
                                //}


                            }
                        }
                        else
                        {
                            ExcelSaves(dt, strsz, patex, subStr);
                            IRB_STATUS = "处理失败";
                            inset(IRB_XLS_FILE, IRB_STATUS, IRB_UPLOAD_NUM, IRB_UPLOAD_USER, IRB_REMARK);
                        }



                    }
                    catch (Exception ex)
                    {
                        //OgilvyOne.Utility.LogsUtil.DoLog(ex.ToString());
                        IRB_STATUS = "处理失败";
                        IRB_REMARK = "导入数据异常";
                        inset(IRB_XLS_FILE, IRB_STATUS, IRB_UPLOAD_NUM, IRB_UPLOAD_USER, IRB_REMARK);
                    }




                    
                    Response.Redirect("camuploads.aspx", true);

                }

                catch (Exception ex)
                {

                    Label1.Text = "发生错误：" + ex.Message.ToString();

                }

            }
            else
            {
                Label1.Text = "只允许上传xls文件！";
            }
        }
        else
        {
            Label1.Text = "请选择要上传的文件！";
        } 




       
    }




  

    //创建DataTable
    private DataTable UpRxcel(System.Data.DataTable dt, string wt, int i)
    {


        //dt.Rows[i][15] = "";
        //获取Employees表中的第4行数据  
        DataRow drEmployee = dt.Rows[i];
        drEmployee.BeginEdit();
        drEmployee["remark"] = wt;
        drEmployee.EndEdit();


        return dt;
    }




    /// <summary>
    /// 单元格格式为日期时间，使用此方法转换为DateTime类型，若解析失败则返回‘0001-01-01’
    /// </summary>
    public static string ParseDateTime(string cellValue)
    {
        DateTime date = default(DateTime);

        double value = default(double);

        if (double.TryParse(cellValue, out value))
        {
            date = DateTime.FromOADate(value);
        }
        else
        {
            DateTime.TryParse(cellValue, out date);
        }

        return date.ToShortDateString();
    }


    public static string ExcelSaves(System.Data.DataTable dt, string[] teRows, string pathName, string tableName)
    {

        if (teRows.Length == 0 || teRows == null || dt == null || dt.Rows.Count == 0 || pathName == "" || tableName == "")
        {
           // OgilvyOne.Utility.LogsUtil.DoLog("调用参数为空！/r/n");
            return null;
        }
        if (teRows.Length != dt.Columns.Count)
        {
            //OgilvyOne.Utility.LogsUtil.DoLog("储存Excel表格列数与导入的DataTable列数不一致！/r/n");
            return null;
        }
        string newPath = tableName + ".xls";

        //当物理路径下不存在同名文件才保存
        if (System.IO.File.Exists(pathName + "" + newPath))
        {
            newPath = tableName + CreateRandom() + ".xls";
        }

        try
        {
            XlsDocument doc = new XlsDocument();
            doc.FileName = newPath;

            org.in2bits.MyXls.Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
            org.in2bits.MyXls.Cell cell = null;
            try
            {
                //生成表头
                for (int i = 0; i < teRows.Length; i++)
                {
                    cell = sheet.Cells.Add(1, i + 1, teRows[i].ToString());
                }

                //生成表数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (dt.Rows[i][j] != null && dt.Rows[i][j].ToString() != "")
                        {
                            cell = sheet.Cells.Add(i + 2, j + 1, dt.Rows[i][j].ToString());

                        }


                    }


                }

            }
            catch (Exception ex)
            {
               // OgilvyOne.Utility.LogsUtil.DoLog("储存Excel表格数据出错!插入数据格式或内容有误！/r/n");
               // OgilvyOne.Utility.LogsUtil.DoLog(ex.ToString());
                return null;
                throw;

            }
            try
            {

                doc.Save(pathName);

            }
            catch (Exception ex)
            {
               // OgilvyOne.Utility.LogsUtil.DoLog("保存Excel表格出错!/r/n");
               // OgilvyOne.Utility.LogsUtil.DoLog(ex.ToString());
                return null;
                throw;
            }



        }
        catch (Exception ex)
        {
            //OgilvyOne.Utility.LogsUtil.DoLog("创建Excel表格出错!/r/n");
           // OgilvyOne.Utility.LogsUtil.DoLog(ex.ToString());
            return null;
            throw;
        }


        return newPath;
    }

    //生成1-6位随机数
    public static string CreateRandom()
    {
        Random random = new Random();
        int i = random.Next(1, 999999);
        return i.ToString();
    }
    //生成5位随机数
    public static string CreateRandom5()
    {
        Random random = new Random();
        int i = random.Next(10000, 99999);
        return i.ToString();
    }

    protected void upLaodOK0_Click(object sender, EventArgs e)
    {
        string pathName = HttpContext.Current.Server.MapPath("excel/") + "模板.xls";
        System.IO.FileInfo fileInfo = new System.IO.FileInfo(pathName);
        if (fileInfo.Exists == false)
        {
            Response.Write("<script>alert('下载文件数据为空!');</script>");
            return;
        }
        Response.Clear();
        Response.ContentType = "application/x-excel";
        Response.AddHeader("Content-Disposition", "attachment;FileName=" + HttpUtility.UrlEncode("模板.xls", System.Text.Encoding.UTF8));
        Response.WriteFile(pathName);

    }
}
